package com.lovo.dao.impl;

import com.lovo.bean.CutPageBean;
import com.lovo.bean.PostBean;
import com.lovo.bean.UserBean;
import com.lovo.dao.IPostDao;
import org.springframework.stereotype.Repository;

import java.sql.SQLException;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;

@Repository
public class PostDaoImpl extends BaseDao implements IPostDao {
    @Override
    public CutPageBean<PostBean> findByItem(int pageNO, Integer channelId, String title, String writer, LocalDate starDate, LocalDate endDate) {
        CutPageBean<PostBean> cutBean = new CutPageBean<>();
        String sql = " where 1=1  ";
        List value = new ArrayList();
        if (title != null && title.length() != 0) {
            sql += "and p_name like ? ";
            value.add(" %" + title + "% ");
        }
        if (writer != null && writer.length() != 0) {
            sql += "and u_name like ? ";
            value.add(" %" + writer + "% ");
        }
        if (starDate != null) {
            sql += " and p_time >= ? ";
            value.add(starDate);
        }
        if (endDate != null) {
            sql += " and p_time <= ? ";
            value.add(endDate);
        }
        this.setConnection();
        try {
            ps = con.prepareStatement("  SELECT  p.* ,COUNT(c.`pk_commentId`) num , l.u_userName userName FROM t_post p JOIN t_user l ON l.pk_userId=p.fk_userId  " +
                    "  LEFT JOIN t_comment c  ON c.`fk_postId`= p.`pk_postId`" + sql + " AND p.`fk_channelId`=? GROUP BY p.`pk_postId` LIMIT ?,? ");
            for (int i = 0; i < value.size(); i++) {
                ps.setObject(i + 1, value.get(i));
            }
            ps.setObject(value.size() + 1, channelId);
            ps.setObject(value.size() + 2, (pageNO - 1) * CutPageBean.PAGESIZE);
            ps.setObject(value.size() + 3, CutPageBean.PAGESIZE);
            rs = ps.executeQuery();
            while (rs.next()) {
                PostBean post = new PostBean();
                post.setId(rs.getInt("pk_postId"));
                String time = rs.getString("p_time");
                String time1 = time.substring(0, 19).replaceAll("T", " ");
                DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
                post.setLocalDate(LocalDateTime.parse(time1, df));
                post.setMsg(rs.getString("p_msg"));
                post.setTheme(rs.getString("p_name"));
                post.setCommentNum(rs.getInt("num"));
                post.getUser().setUserName(rs.getString("userName"));
                cutBean.getList().add(post);
            }
            ps = con.prepareStatement("SELECT  count(*) k FROM t_post  p LEFT JOIN t_user t ON t.pk_userId = p.fk_userId LEFT JOIN t_comment c ON c.fk_postId = p.pk_postId  "+ sql+" and  fk_channelId= ?");
            for (int i = 0; i < value.size(); i++) {
                ps.setObject(i + 1, value.get(i));
            }
            ps.setObject(value.size() + 1, channelId);
            rs = ps.executeQuery();
            if (rs.next()) {
                cutBean.setCount(rs.getInt("k"));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            this.closeConnection();
        }
        System.out.println(cutBean);
        return cutBean;
    }

    @Override
    public void addPost(PostBean post) {
        this.setConnection();
        try {
            ps = con.prepareStatement("INSERT INTO t_post(p_name,p_msg,p_time,fk_userId,fk_channelId)VALUES(?,?,?,?,?)");
            ps.setObject(1, post.getTheme());
            ps.setObject(2, post.getMsg());
            ps.setObject(3, post.getLocalDate());
            ps.setObject(4, post.getUser().getUserId());
            ps.setObject(5, post.getChannel().getId());

            ps.executeUpdate();

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            this.closeConnection();
        }
    }

    @Override
    public PostBean findById(Integer id) {
        PostBean postBean = null;
        this.setConnection();
        try {
            ps = con.prepareStatement("SELECT * FROM t_post WHERE pk_postId = ? ");
            ps.setObject(1, id);
            rs = ps.executeQuery();

            if (rs.next()) {
                postBean = new PostBean();
                postBean.setId(rs.getInt("pk_postId"));
                postBean.setTheme(rs.getString("p_name"));
                postBean.setMsg(rs.getString("p_name"));
                postBean.setLocalDate(LocalDateTime.from(LocalDate.parse(rs.getString("p_time"))));
                postBean.getUser().setUserId(rs.getInt("fk_userId"));
                postBean.getChannel().setId(rs.getInt("fk_channelId"));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            this.closeConnection();
        }

        return postBean;
    }
}
